<%@ page language="java" import="java.sql.*" %>

<%@ include file="../jsp-includes/Header.jsp" %>

<jsp:useBean id="myStrings" scope="request" class="com.erclab.internal.xpresso.util.ManStrings" />


<%  String action = request.getParameter("action");
    String formName = request.getParameter("formName");
    String uniqueKeyName = request.getParameter("uniqueKeyName");
    String uniqueKeyValue = request.getParameter("uniqueKeyValue");
    String parentKeyValue = request.getParameter("parentKeyValue");
    String forwardUrl = request.getParameter("forwardUrl");

    String Table = "";
    String valueColumnLookup;
    String TableOld = "";
    String sql = "";
    String sql2 = "";
    String FieldContent;
    String ContentType = "";
    String[] contentMultiple;
    String destinationColumn;
    String forwardUniqueKeyValue = null;
    boolean flagUrl = false;

    // Generate a unique key for this page in order to relate records
    java.util.Random random = new java.util.Random();
    String uniqueIdPage = random.nextInt() + new java.util.Date().toString();

    // Check the forward URL
    Cookie[] myCookie = request.getCookies();
    if (forwardUrl != null){ 
        if (forwardUrl.equals("") || forwardUrl.equalsIgnoreCase("null")){
            for (int i = 0; i < myCookie.length; i++){
                myCookie[i].setPath(request.getContextPath());
                if (myCookie[i].getName().equals("forwardUrl")){
                        forwardUrl = myCookie[i].getValue();
                }
            }
        } else {
            // We need to add the uniqueKeyValue inserted or updated
            flagUrl = true;
        }
    } else {
        forwardUrl = myCookie[0].getValue();
    }


    boolean isThereNext = true;
    int count = 0;
    ResultSet rs = null;

    if (action == null){ action = "";}

    sql = "SELECT IdFormElement, DestinationTable, DestinationColumn, ContentType, elementType, valueColumnLookup, inputMask FROM App_FormElements, App_Forms WHERE formName='" + formName
    + "' and App_FormElements.IdForm=App_Forms.IdForm and ElementType <> 'MultiPle' and ElementType <> 'Static' and ElementType <> 'ShowValue' ORDER BY DestinationTable";
    rs = dbCon.execSQL(sql);
    if(rs.next())
    {
        Table = rs.getString("DestinationTable");
        isThereNext = true;
    }else{
        isThereNext = false;
    }
    String sqlSelect = "SELECT " + uniqueKeyName + " FROM " + Table + " WHERE ";
    if ( action.equalsIgnoreCase("Insert") ) {
       while (isThereNext){
            TableOld = Table;
            sql = "INSERT INTO " + Table + " (";
            sql2 = "";
            do {
                valueColumnLookup = rs.getString("valueColumnLookup");
                if (rs.getString("elementType").equalsIgnoreCase("Hidden") && valueColumnLookup != null) {
                    if (valueColumnLookup.indexOf("param:uniqueId") > 0){
                        FieldContent = valueColumnLookup.replaceAll("-param:uniqueId-", uniqueIdPage);
                    } else {
                        FieldContent = request.getParameter("Field" + rs.getString("IdFormElement"));
                    }
                } else {
                    FieldContent = request.getParameter("Field" + rs.getString("IdFormElement"));
                }
                ContentType = rs.getString("ContentType");
                destinationColumn = rs.getString("DestinationColumn");
                sql = sql + destinationColumn + ", ";
                if (FieldContent == null || FieldContent.equalsIgnoreCase("") || FieldContent.equalsIgnoreCase("null") ){
                    FieldContent = "";
                }
                if ( ContentType.equalsIgnoreCase("Number")){
                    if (FieldContent.equalsIgnoreCase("-") || FieldContent.equalsIgnoreCase("") ){
                        sql2 = sql2 + "0, ";
                        sqlSelect += destinationColumn + "=0";
                    }else{
                        sql2 = sql2 + FieldContent + ", ";
                        sqlSelect += destinationColumn + "=" + FieldContent;
                    }
                } else if ( ContentType.equalsIgnoreCase("String")){
                    FieldContent = FieldContent.replaceAll("'", "''");
                    FieldContent = FieldContent.replaceAll("\\$", "&#0036;");
                    sql2 = sql2 + "'" + FieldContent + "', ";
                    sqlSelect += destinationColumn + "='" + FieldContent + "'";
                } else if ( ContentType.equalsIgnoreCase("Date")){
                    if ( FieldContent.equalsIgnoreCase("") ){
                        sql2 = sql2 + "Null, ";
                        sqlSelect += destinationColumn + "=null";
                    } else {
                        FieldContent = myStrings.toMySqlDate(FieldContent);
                        sql2 = sql2 + "'" + FieldContent + "', ";
                        sqlSelect += destinationColumn + "='" + FieldContent + "' ";
                    }
                }
                if(rs.next()){
                    isThereNext = true;
                    Table = rs.getString("DestinationTable");
                    sqlSelect += " AND ";
                } else {
                    isThereNext = false;
                    Table = "EndOfRecords";
                }
            } while (Table.equalsIgnoreCase(TableOld));
            sql = sql.substring(0, (sql.length()-2));
            sql2 = sql2.substring(0, (sql2.length()-2)) + ")";
            sql = sql + ") values(" + sql2;
            //Execute Query
            dbCon.execInsert(sql, netId);
        }
        // Get the Id of the last record inserted to be sent with the forward URL
        if (flagUrl){
            rs = dbCon.execSQL("SELECT LAST_INSERT_ID() FROM " + TableOld);
            if (rs.next()){
                if(forwardUrl.indexOf("?") > 0){
                forwardUrl += "&uniqueKeyValue=" + rs.getString(1);
                } else {
                    forwardUrl += "?uniqueKeyValue=" + rs.getString(1);
                }
            } else {
                throw new javax.servlet.jsp.JspException ("[810] Error retrieving the ID for record inserted");
            }
        }
    } else if ( action.equalsIgnoreCase("Update") ) {
        sql = "";
        while (isThereNext){
            TableOld = Table;
            sql = "UPDATE " + Table + " set ";
            do {
                valueColumnLookup = rs.getString("valueColumnLookup");
                if (rs.getString("elementType").equalsIgnoreCase("Hidden") && valueColumnLookup != null) {
                    if (valueColumnLookup.indexOf("param:uniqueId") > 0){
                        FieldContent = valueColumnLookup.replaceAll("-param:uniqueId-", uniqueIdPage);
                    } else {
                        FieldContent = request.getParameter("Field" + rs.getString("IdFormElement"));
                    }
                } else {
                    FieldContent = request.getParameter("Field" + rs.getString("IdFormElement"));
                }
                ContentType = rs.getString("ContentType");
                sql = sql + rs.getString("DestinationColumn") + "=";
                if (FieldContent == null || FieldContent.equalsIgnoreCase("") || FieldContent.equalsIgnoreCase("null") ){
                    FieldContent = "";
                }
                if ( ContentType.equalsIgnoreCase("Number")){
                    if (FieldContent.equalsIgnoreCase("-") || FieldContent.equalsIgnoreCase("")){
                        sql = sql + "0, ";
                    }else {
                        sql = sql + FieldContent + ", ";
                    }
                } else if ( ContentType.equalsIgnoreCase("String")){
                    FieldContent = FieldContent.replaceAll("\\$", "&#0036;");
                    sql = sql + "'" + FieldContent.replaceAll("'", "''") + "', ";
                } else if ( ContentType.equalsIgnoreCase("Date")){
                    if ( FieldContent.equalsIgnoreCase("") ){
                        sql = sql + "Null, ";
                    } else {
                        FieldContent = myStrings.toMySqlDate(FieldContent);
                        sql = sql + "'" + FieldContent + "', ";
                    }
                }
                if(rs.next()){
                    isThereNext = true;
                    Table = rs.getString("DestinationTable");
                } else {
                    isThereNext = false;
                    Table = "EndOfRecords";
                }
            } while (Table.equalsIgnoreCase(TableOld));
            sql = sql.substring(0, (sql.length()-2));
            if ( myStrings.isNumeric(uniqueKeyValue)){
                sql = sql + " WHERE " + uniqueKeyName + "=" + uniqueKeyValue;
            } else {
                sql = sql + " WHERE " + uniqueKeyName + "='" + uniqueKeyValue + "'";
            }
            //Execute Query
            dbCon.execInsert(sql, netId);
        }
        if (flagUrl){
            if(forwardUrl.indexOf("?") > 0){
                forwardUrl += "&uniqueKeyValue=" + uniqueKeyValue + "&parentKeyValue=" + parentKeyValue;
            } else {
                forwardUrl += "?uniqueKeyValue=" + uniqueKeyValue + "&parentKeyValue=" + parentKeyValue;
            }
        }
    }

    // INSERT OR UPDATE MULTI - OPTION FIELDS
    sql = "SELECT IdFormElement, DestinationTable, DestinationColumn FROM App_FormElements, App_Forms WHERE formName='" + formName
    + "' and App_FormElements.IdForm=App_Forms.IdForm and ElementType='Multiple' ORDER BY DestinationTable";
    rs = dbCon.execSQL(sql);
    while (rs.next()){
        contentMultiple = request.getParameterValues("Field" + rs.getString("IdFormElement"));
        destinationColumn = rs.getString("DestinationColumn");
        Table = rs.getString("DestinationTable");
        if (action.equalsIgnoreCase("Update")){
            sql = "DELETE FROM " + Table + " WHERE " + uniqueKeyName + "=" + uniqueKeyValue;
            dbCon.execInsert(sql, netId);
        }
        if (contentMultiple != null) {
            for(int i=0;i< contentMultiple.length;i++){
                // Insert new elements
                if (uniqueKeyValue == null || uniqueKeyValue.equals("null")){
                    if (forwardUniqueKeyValue != null){
                        uniqueKeyValue = forwardUniqueKeyValue;
                    }else {
                        rs = dbCon.execSQL(sqlSelect);
                        if (rs.next()){
                            uniqueKeyValue = rs.getString(1);
                        } else {
                            throw new javax.servlet.jsp.JspException ("ERROR 103 No uniqueKeyValue or uniqueKeyName specified. Is not possible to insert records");
                        }
                    }
                }
                sql = "INSERT INTO " + Table + " (" + destinationColumn + ", "
                + uniqueKeyName + ") values(" + contentMultiple[i] + ", " + uniqueKeyValue + ")";
                dbCon.execInsert(sql, netId);
            }
        }
    }
    rs.close();

%>
<jsp:forward page="<%=forwardUrl%>" />

<%@ include file="../jsp-includes/footer.jsp" %>





